package gob.inei.enaho.model.service;

import gob.inei.enaho.model.domain.tiempo.Mes;
import gob.inei.enaho.model.domain.tiempo.Periodo;
import gob.inei.enaho.model.domain.ubicacion.AreaGeografica;
import gob.inei.enaho.model.domain.ubicacion.Conglomerado;
import gob.inei.enaho.model.domain.ubicacion.Odei;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import util.android.sqlite.CommandSQLite;
import util.android.sqlite.SQLiteUtil;
import util.form.respuesta.RegistroImpl;
import util.patron.command.Command1;
import util.www.sql.WhereSqlDinamico;

import android.database.Cursor;


public class ConglomeradoService 
extends AbstractService
{
	
	public List<Integer> findAnhos() throws SQLException {
		final List<Integer> lista = new ArrayList<Integer>();
		String sql = "select distinct anho from Campo_Conglomerado";
		SQLiteUtil.executeQuery(this.getOpenHelper(),sql,new Command1<Cursor>() {			
			@Override
			public void execute(Cursor obj) throws Exception {
				String anho = SQLiteUtil.getString(obj, "anho"); 
				lista.add(Integer.parseInt(anho));
			}
		});
		return lista;
	}

	public List<Conglomerado> findMarco() throws SQLException {				
		
		String sql = "select * from Campo_Conglomerado C\n" +
				"left join odei OD\n" +
				"	on C.Odei=OD.CodOdei ";
		
		final List<Conglomerado> lista = new ArrayList<Conglomerado>();
		
		SQLiteUtil.executeQuery(this.getOpenHelper(), sql, new Command1<Cursor>() {			
			@Override
			public void execute(Cursor cursor) {
				Conglomerado c = new Conglomerado();
				c.setCodigo(SQLiteUtil.getString(cursor,"Conglome"));
				c.setAnho(SQLiteUtil.getString(cursor,"Anho"));
				
				Mes m = new Mes();
				m.setCodigo(SQLiteUtil.getString(cursor,"Mes"));
				c.setMes(m);
				
				Periodo p = new Periodo();
				p.setCodigo(SQLiteUtil.getString(cursor,"Periodo"));
				c.setPeriodo(p);
				
				Odei o = new Odei();
				/*
				int d = cursor.getColumnIndex("NomOdei");				
				String v = cursor.getString(d);
				System.out.println("d="+d+" v="+v);
				*/
				o.setNombre(SQLiteUtil.getString(cursor,"NomOdei"));
				c.setOdei(o);
				
				lista.add(c);
			}
		});		
		return lista;
	}

	public List<Conglomerado> findByFilter(RegistroImpl filtro) throws SQLException {
		final List<Conglomerado> lista = new ArrayList<Conglomerado>();
		WhereSqlDinamico w = new WhereSqlDinamico();
		w.and("Odei=",filtro.getValorString("odei"));
		w.and("Anho=",filtro.getValorString("anho"));
		w.and("Mes=",filtro.getValorString("mes"));
		w.and("Periodo=",filtro.getValorString("periodo"));
		
		String sql = "select * from Campo_Conglomerado "+w.getSql();
		SQLiteUtil.executeQuery(this.getOpenHelper(),sql,new Command1<Cursor>() {			
			@Override
			public void execute(Cursor obj) throws Exception {
				Conglomerado c = new Conglomerado();
				c.setCodigo(SQLiteUtil.getString(obj,"Conglome"));
				c.setTipoSeleccion(SQLiteUtil.getString(obj,"TipoSeleccion"));
				c.setAreaGeografica(AreaGeografica.findByCodigo(SQLiteUtil.getString(obj,"sede")));
				lista.add(c);
			}
		});
		return lista;
	}

	public Conglomerado findById(String conglomerado) throws SQLException {
		final Conglomerado c = new Conglomerado();
		String sql = "Select Estrato From Campo_Conglomerado Where Conglome='" + conglomerado + "'";
		SQLiteUtil.executeQueryUnaVez(getOpenHelper(), sql, new CommandSQLite() {			
			@Override
			public Object execute(Cursor cr) throws SQLException {
				c.setEstrato( SQLiteUtil.getString(cr, "Estrato") );
				return null;
			}
		});
		return c;
	}

	@Override
	protected String getNombreTabla() {
		return "Campo_Conglomerado";
	}
	
	
}
